package com.css.fxfzypg.modules.baseData.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.KeyProjectEntity;
import com.css.fxfzypg.modules.baseData.queryParams.KeyProjectQueryParams;
import com.css.fxfzypg.modules.baseData.repository.KeyProjectRepository;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-24 10:37
 */
@Repository
public class KeyProjectRepositoryImpl implements KeyProjectRepository {


    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    SysRoleService sysRoleService;

    @Autowired
    SUserService sUserService;

    @Override
    public void saveKeyProject(KeyProjectEntity keyProjectEntity) {
        String sql = "";
        sql = "insert into data_key_project values(:dkpId, :dkpProvince, :dkpCity, :dkpPrefecture, :dkpKeyType, :dkpName, :dkpLocation, " +
                ":dkpType, :dkpScale, :dkpYear, :dkpCondition, :dkpActuality, :dkpTransType, :dkpLong, :dkpSpan, :dkpCreateUser, " +
                ":dkpCreateTime, :dkpUpdateUser, :dkpUpdateTime, :dkpIsDelete, :dkpProvinceId, :dkpCityId, :dkpPrefectureId,:provinceCode, :cityCode, :prefectureCode)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(keyProjectEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public KeyProjectEntity getKeyProjectById(String id) {
        String sql = "select * from data_key_project where dkp_id= ? ";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(KeyProjectEntity.class), id);
    }

    @Override
    public void updateKeyProject(KeyProjectEntity keyProjectEntity) {
        String sql = "update data_key_project set dkp_province =:dkpProvince ,dkp_city =:dkpCity ,dkp_prefecture =:dkpPrefecture " +
                ",dkp_key_type =:dkpKeyType ,dkp_name =:dkpName ,dkp_location =:dkpLocation ,dkp_type =:dkpType ,dkp_scale =:dkpScale " +
                ",dkp_year =:dkpYear ,dkp_condition=:dkpCondition ,dkp_actuality =:dkpActuality ,dkp_trans_type =:dkpTransType  " +
                ",dkp_span=:dkpSpan ,dkp_long=:dkpLong ,dkp_update_user =:dkpUpdateUser, dkp_update_time =:dkpUpdateTime " +
                ",dkp_province_id =:dkpProvinceId ,dkp_city_id =:dkpCityId ,dkp_prefecture_id =:dkpPrefectureId " +
                "where dkp_id='" + keyProjectEntity.getDkpId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(keyProjectEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void deleteKeyProject(List<String> ids) {
        String sql = "update data_key_project set dkp_is_delete = '" + YNEnum.Y.toString() + "'where dkp_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public Map<String, Object> getAllKeyProjectByParams(KeyProjectQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder(" select * from data_key_project ");
//        StringBuilder orderSql = new StringBuilder(" order by prefecture_code");
        StringBuilder orderSql = new StringBuilder();
        if ("0".equals(queryParams.getOption())) {
            orderSql.append(" order by province_code,city_code,prefecture_code,dkp_id");
        }
        if ("1".equals(queryParams.getOption())) {
            orderSql.append(" order by COALESCE (dkp_update_time,dkp_create_time) desc");
        }
        StringBuilder whereSql = new StringBuilder(" where dkp_is_delete = '" + YNEnum.N.toString() + "'");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;

        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode") + ",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.YPG_PRI_YW_ROLE) || roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_YW_ROLE)) {
            whereSql.append(" and dkp_province_id = '" + sUser.getProvinceId() + "'");
        }

        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(queryParams.getDkpProvinceId())) {
            whereSql.append(" and dkp_province_id = '" + queryParams.getDkpProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDkpCityId())) {
            whereSql.append(" and dkp_city_id = '" + queryParams.getDkpCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDkpPrefectureId())) {
            whereSql.append(" and dkp_prefecture_id = '" + queryParams.getDkpPrefectureId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDkpKeyType())) {
            whereSql.append(" and dkp_key_type = '" + queryParams.getDkpKeyType() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(KeyProjectEntity.class)).size();
        List<KeyProjectEntity> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(KeyProjectEntity.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<KeyProjectEntity> exportExcel() {
        return jdbcTemplate.query("select u.* from data_key_project u  where u.dkp_isdelete='" + YNEnum.Y.toString() + "'", new BeanPropertyRowMapper<>(KeyProjectEntity.class));
    }

    @Override
    public void importExcel(List<KeyProjectEntity> keyProjectEntities) {
        /*jdbcTemplate.batchUpdate("insert into data_key_project (dkp_id,dkp_province,dkp_city,dkp_prefecture,dkp_key_type,dkp_name,dkp_location,dkp_type,dkp_scale,dkp_year,dkp_condition,dkp_actuality,dkp_trans_type,dkp_project_name,dkp_project_location,dkp_project_type,dkp_project_long,dkp_project_year,dkp_project_condition,dkp_project_actuality,dkp_createtime,dkp_isdelete)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1,keyProjectEntities.get(i).getDkpId());
                ps.setString(2,keyProjectEntities.get(i).getDkpProvince());
                ps.setString(3,keyProjectEntities.get(i).getDkpCity());
                ps.setString(4,keyProjectEntities.get(i).getDkpPrefecture());
                ps.setString(5,keyProjectEntities.get(i).getDkpKeyType());
                ps.setString(6,keyProjectEntities.get(i).getDkpName());
                ps.setString(7,keyProjectEntities.get(i).getDkpLocation());
                ps.setString(8,keyProjectEntities.get(i).getDkpType());
                ps.setString(9,keyProjectEntities.get(i).getDkpScale());
                ps.setString(10,keyProjectEntities.get(i).getDkpYear());
                ps.setString(11,keyProjectEntities.get(i).getDkpCondition());
                ps.setString(12,keyProjectEntities.get(i).getDkpActuality());
                ps.setString(13,keyProjectEntities.get(i).getDkpTransType());
                ps.setString(14,keyProjectEntities.get(i).getDkpProjectName());
                ps.setString(15,keyProjectEntities.get(i).getDkpProjectLocation());
                ps.setString(16,keyProjectEntities.get(i).getDkpProjectType());
                ps.setString(17,keyProjectEntities.get(i).getDkpProjectLong());
                ps.setString(18,keyProjectEntities.get(i).getDkpProjectYear());
                ps.setString(19,keyProjectEntities.get(i).getDkpProjectCondition());
                ps.setString(20,keyProjectEntities.get(i).getDkpProjectActuality());
                java.sql.Date sqlDate = new java.sql.Date(keyProjectEntities.get(i).getDkpCreatetime().getTime());
                ps.setDate(21,  sqlDate);
                ps.setString(22,keyProjectEntities.get(i).getDkpIsdelete());
            }

            @Override
            public int getBatchSize() {
                return keyProjectEntities.size();
            }
        });*/
    }

    @Override
    public KeyProjectEntity findkeyProjectByProAndCityAndPre(String dkpProvince, String dkpCity, String dkpPrefecture) {

        String sql = "select dkp_id from data_key_project where dkp_province = ? and dkp_city = ? and dkp_prefecture = ?";
        KeyProjectEntity keyProjectEntity = new KeyProjectEntity();
        try {
            keyProjectEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(KeyProjectEntity.class), dkpProvince, dkpCity, dkpPrefecture);
            return keyProjectEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<String> findFormDataListByDkpKeyType(KeyProjectQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select dkp_id from data_key_project ");
//        StringBuilder orderSql = new StringBuilder(" order by dkp_create_time desc");
        StringBuilder orderSql = new StringBuilder();
        if ("0".equals(queryParams.getOption())) {
            orderSql.append(" order by province_code,city_code,prefecture_code");
        }
        if ("1".equals(queryParams.getOption())) {
            orderSql.append(" order by COALESCE (dkp_update_time,dkp_create_time) desc");
        }
        StringBuilder whereSql = new StringBuilder(" where dkp_is_delete = '" + YNEnum.N.toString() + "'");

        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode") + ",");
        }
        String roles = str.toString();
        if (!roles.contains(FxfzConstants.YPG_COUN_YW_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_GL_ROLE)) {
            whereSql.append(" and dkp_province_id = '" + sUser.getProvinceId() + "'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getDkpProvinceId())) {
            whereSql.append(" and dkp_province_id = '" + queryParams.getDkpProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDkpCityId())) {
            whereSql.append(" and dkp_city_id = '" + queryParams.getDkpCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDkpPrefectureId())) {
            whereSql.append(" and dkp_prefecture_id = '" + queryParams.getDkpPrefectureId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getDkpKeyType())) {
            whereSql.append(" and dkp_key_type = '" + queryParams.getDkpKeyType() + "'");
        }
        sql.append(whereSql).append(orderSql);
        List<String> list = jdbcTemplate.queryForList(sql.toString(), String.class);
        return list;
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_key_project where dkp_prefecture = ? and dkp_city = ? and dkp_province = ? and dkp_is_delete ='0'", Integer.class, county, city, province);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_key_project where dkp_city = '" + city + "' and dkp_province = '" + province + "' and dkp_is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String name) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_key_project where dkp_province = '" + name + "' and dkp_is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_key_project where dkp_province = ? and (TO_CHAR(dkp_update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(dkp_create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and dkp_is_delete ='0'", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }

}
